#!/bin/bash

# 定义变量方便修改
table_db=cy_database
hive=/home/software/hive-2.3.6/bin/hive

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
    do_date=$1
else
    do_date=`date -d "-1 day" +%F`
fi

sql="
set hive.exec.dynamic.partition.mode=nonstrict;

with
tmp_unemploy_person as
(
	select
		id,
		name ,
		sex ,
		birthday,
		cast(round(months_between(current_date(),birthday)/12) as string) as age,
		null nation,
		idcard,
		null census_register_area_code,
		null census_type,
		now_address area_code,
		phone,
		null contact_qq,
		null contact_wei,
		education,
		school,
		edu_time,
		graduation ,
		qcellcore census_register,
		null home_address,
		cast(round( months_between(current_date(),edu_time)/12) as string) as work_year,
		null startup_code,
		cast('0' as string) personal_state,
		now_address,
		unemploy_register_time ,
		unemploy_cause ,
		unemploy_time,
		old_company ,
		sign ,
		update_time,
		card_photo_back ,
		card_photo_obverse,
		hard_special_certificate_1 ,
		hard_special_certificate_2,
		hard_special_certificate_3 ,
		examine_status ,
		hard_situation ,
		is_allowance_family,
		cast(current_date() as string) undate_time
	from cy_database.dwd_unemploy_person up
	where date_format(up.dt,'yyyy-MM-dd')='2020-06-17'
),

tmp_person_apply_job as
(
	select
		id,
		name,
		sex,
		birthday,
		cast (round(months_between(current_date(),concat_ws('-',substring(idcard,7,4) , substring(idcard,11,2),substring(idcard,13,2)))/12) as string)  as age,
		null nation,
		idcard,
		registered_city_code as census_register_area_code,
		null census_type,
		null area_code,
		phone,
		null contact_qq ,
		null contact_wei,
		education ,
		null school,
		null edu_time,
		null graduation,
		registered_city_code as census_register ,
		null home_address,
		work_year,
		null startup_code ,
		now_work_situation personal_state,
		now_city_code as now_address,
		update_time unemploy_register_time,
		null unemploy_cause,
		update_time unemploy_time	,
		null old_company,
		null  sign,
		update_time,
		null card_photo_back,
		null card_photo_obverse ,
		null hard_special_certificate_1,
		null hard_special_certificate_2,
		null hard_special_certificate_3 ,
		null examine_status,
		null hard_situation ,
		null is_allowance_family,
		update_time undate_time
	from cy_database.dwd_person_apply_job paj
	where personal_state = 0 and date_format(paj.dt,'yyyy-MM-dd')='2020-06-17'
)

insert overwrite table cy_database.dws_person_unemploy_detail partition(dt ='2020-06-17')
		select
			id,
			name ,
			sex ,
			birthday,
			age,
			nation,
			idcard,
			census_register_area_code,
			census_type,
			area_code,
			phone,
			contact_qq,
			contact_wei,
			education,
			school,
			edu_time,
			graduation ,
			census_register,
			home_address,
			work_year,
			startup_code,
			personal_state,
			now_address,
			unemploy_register_time ,
			unemploy_cause ,
			unemploy_time,
			old_company ,
			sign ,
			update_time,
			card_photo_back ,
			card_photo_obverse,
			hard_special_certificate_1 ,
			hard_special_certificate_2,
			hard_special_certificate_3 ,
			examine_status ,
			hard_situation ,
			is_allowance_family,
			undate_time
		from tmp_unemploy_person
		union all
		select
			id,
			name ,
			sex ,
			birthday,
			age,
			nation,
			idcard,
			census_register_area_code,
			census_type,
			area_code,
			phone,
			contact_qq,
			contact_wei,
			education,
			school,
			edu_time,
			graduation ,
			census_register,
			home_address,
			work_year,
			startup_code,
			personal_state,
			now_address,
			unemploy_register_time ,
			unemploy_cause ,
			unemploy_time,
			old_company ,
			sign ,
			update_time,
			card_photo_back ,
			card_photo_obverse,
			hard_special_certificate_1 ,
			hard_special_certificate_2,
			hard_special_certificate_3 ,
			examine_status ,
			hard_situation ,
			is_allowance_family,
			undate_time
		from tmp_person_apply_job
;


"

$hive -e "$sql"